package com.music.note_android.data.database.dao

import androidx.room.*
import com.music.note_android.data.model.Folder
import com.music.note_android.data.model.FolderWithNoteCount
import kotlinx.coroutines.flow.Flow

/**
 * 文件夹数据访问对象
 * 定义文件夹相关的数据库操作
 */
@Dao
interface FolderDao {
    
    /**
     * 获取所有文件夹
     */
    @Query("SELECT * FROM folders ORDER BY created_time ASC")
    suspend fun getAllFolders(): List<Folder>
    
    /**
     * 获取所有文件夹的Flow
     */
    @Query("SELECT * FROM folders ORDER BY created_time ASC")
    fun getAllFoldersFlow(): Flow<List<Folder>>
    
    /**
     * 获取文件夹及其笔记数量
     */
    @Query("""
        SELECT f.id, f.name, f.created_time, f.is_system,
               COUNT(n.id) as note_count
        FROM folders f
        LEFT JOIN notes n ON f.id = n.folder_id
        GROUP BY f.id, f.name, f.created_time, f.is_system
        ORDER BY f.created_time ASC
    """)
    suspend fun getFoldersWithNoteCount(): List<FolderWithNoteCount>
    
    /**
     * 获取文件夹及其笔记数量的Flow
     */
    @Query("""
        SELECT f.id, f.name, f.created_time, f.is_system,
               COUNT(n.id) as note_count
        FROM folders f
        LEFT JOIN notes n ON f.id = n.folder_id
        GROUP BY f.id, f.name, f.created_time, f.is_system
        ORDER BY f.created_time ASC
    """)
    fun getFoldersWithNoteCountFlow(): Flow<List<FolderWithNoteCount>>
    
    /**
     * 根据ID获取文件夹
     */
    @Query("SELECT * FROM folders WHERE id = :folderId")
    suspend fun getFolderById(folderId: String): Folder?
    
    /**
     * 根据名称获取文件夹
     */
    @Query("SELECT * FROM folders WHERE name = :name")
    suspend fun getFolderByName(name: String): Folder?
    
    /**
     * 检查文件夹名称是否存在（排除指定ID）
     */
    @Query("SELECT COUNT(*) FROM folders WHERE name = :name AND id != :excludeId")
    suspend fun countFoldersByNameExcluding(name: String, excludeId: String): Int
    
    /**
     * 检查文件夹名称是否存在
     */
    @Query("SELECT COUNT(*) FROM folders WHERE name = :name")
    suspend fun countFoldersByName(name: String): Int
    
    /**
     * 获取文件夹中的笔记数量
     */
    @Query("SELECT COUNT(*) FROM notes WHERE folder_id = :folderId")
    suspend fun getNoteCountInFolder(folderId: String): Int
    
    /**
     * 获取未分类笔记数量
     */
    @Query("SELECT COUNT(*) FROM notes WHERE folder_id IS NULL")
    suspend fun getUncategorizedNoteCount(): Int
    
    /**
     * 获取所有笔记数量
     */
    @Query("SELECT COUNT(*) FROM notes")
    suspend fun getAllNoteCount(): Int
    
    /**
     * 插入文件夹
     */
    @Insert
    suspend fun insertFolder(folder: Folder)
    
    /**
     * 更新文件夹
     */
    @Update
    suspend fun updateFolder(folder: Folder)
    
    /**
     * 删除文件夹
     */
    @Delete
    suspend fun deleteFolder(folder: Folder)
    
    /**
     * 根据ID删除文件夹
     */
    @Query("DELETE FROM folders WHERE id = :folderId")
    suspend fun deleteFolderById(folderId: String)
    
    /**
     * 将笔记移动到指定文件夹
     */
    @Query("UPDATE notes SET folder_id = :folderId WHERE id = :noteId")
    suspend fun moveNoteToFolder(noteId: String, folderId: String?)
    
    /**
     * 批量将笔记移动到指定文件夹
     */
    @Query("UPDATE notes SET folder_id = :folderId WHERE id IN (:noteIds)")
    suspend fun moveNotesToFolder(noteIds: List<String>, folderId: String?)
    
    /**
     * 将文件夹中的所有笔记移动到未分类
     */
    @Query("UPDATE notes SET folder_id = NULL WHERE folder_id = :folderId")
    suspend fun moveAllNotesFromFolderToUncategorized(folderId: String)
}